﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Dm;

namespace OneClick
{
    public partial class 索引信息 : Form
    {
        static DmConnection cnn = new DmConnection();
        public 索引信息()
        {
            InitializeComponent();
        }

        private void 索引信息_Load(object sender, EventArgs e)
        {
            string Tab = Form1.Tab;
            string Sch = Form1.Sch;
            //添加 索引列类型 //string sql2 = "WITH AA AS (select INDEX_NAME,LISTAGG(COLUMN_NAME,',')WITHIN GROUP (ORDER BY COLUMN_POSITION) COL from DBA_ind_columns where table_name=trim(upper('"+Tab.Replace('\t',' ')+ "')) and table_owner=trim(upper('" + Sch.Replace('\t', ' ') + "')) GROUP BY TABLE_OWNER,TABLE_NAME,INDEX_NAME ),BB AS (SELECT C.NAME,C.CRTDATE FROM SYSObjects a,sysobjects b,sysobjects c where a.id=b.schid and c.pid=b.id AND C.SUBTYPE$='INDEX' and b.name=trim(upper('" + Tab.Replace('\t', ' ') + "')) AND a.name=trim(upper('" + Sch.Replace('\t', ' ') + "'))  ) SELECT bb.NAME,col FROM AA,BB WHERE AA.INDEX_NAME(+)=BB.NAME AND COL IS NOT NULL";
            string sql2 = "WITH AA AS(select INDEX_NAME, LISTAGG(COLUMN_NAME || ' ' || lower(COLUMN_type||' '||DESCEND), ',')WITHIN GROUP(ORDER BY COLUMN_POSITION) COL from(SELECT O4.NAME INDEX_OWNER, O1.NAME INDEX_NAME, O4.NAME TABLE_OWNER, O2.NAME TABLE_NAME, COL.NAME COLUMN_NAME, COL.TYPE$ COLUMN_TYPE, CAST(SF_GET_INDEX_KEY_SEQ(I.KEYNUM, I.KEYINFO, COL.COLID) AS NUMBER) COLUMN_POSITION, CAST(COL.LENGTH$ AS NUMBER) COLUMN_LENGTH, CAST(CASE WHEN COL.TYPE$ IN('CHAR', 'VARCHAR', 'CHARACTER', 'VARCHAR2') THEN COL.LENGTH$ ELSE 0 END AS NUMBER) CHAR_LENGTH, CASE WHEN SF_GET_INDEX_KEY_ORDER(I.KEYNUM, I.KEYINFO, COL.COLID) = 'A' THEN 'ASC' ELSE 'DESC' END DESCEND FROM SYS.SYSOBJECTS O1 left join SYS.SYSINDEXES I on o1.id = i.id, SYS.SYSOBJECTS O2 left join SYS.SYSCOLUMNS COL on O2.ID = COL.ID, SYS.SYSOBJECTS O3, SYS.SYSOBJECTS O4 WHERE O2.ID = O1.PID AND O3.ID = O2.SCHID AND O4.ID = O3.PID AND SF_COL_IS_IDX_KEY(I.KEYNUM, I.KEYINFO, COL.COLID) = 1)DBA_ind_columns where table_name = trim(upper('" + Tab.Replace('\t', ' ') + "')) and table_owner = trim(upper('" + Sch.Replace('\t', ' ') + "')) GROUP BY TABLE_OWNER, TABLE_NAME, INDEX_NAME) , BB AS (SELECT C.NAME, C.CRTDATE FROM SYSObjects a, sysobjects b, sysobjects c where a.id = b.schid and c.pid = b.id AND C.SUBTYPE$= 'INDEX' and b.name = trim(upper('" + Tab.Replace('\t', ' ') + "')) AND a.name = trim(upper('" + Sch.Replace('\t', ' ') + "')) ) SELECT INDEX_NAME, col, left(bb.crtdate, 10) ctime FROM AA, BB WHERE AA.INDEX_NAME(+) = BB.NAME AND COL IS NOT NULL";
            cnn.ConnectionString = Form1.sstr;
            cnn.Open();
            DmCommand command = new DmCommand();
            command.Connection = cnn;
            try
            {
                string a;
                string b;
                string c;
                command.CommandText = sql2;
                DmDataReader reader = command.ExecuteReader();
                //foreach (string item in listBox2.Items) //错误的用法
                //{
                //    listBox2.Items.Remove(item);
                //}
                while (listBox1.Items.Count > 0)
                {
                    listBox1.Items.Remove(listBox1.Items[0]);
                }
                int i = 0;
                while (reader.Read())
                {
                    a = reader.GetString(0);
                    b = reader.GetString(1);
                    c = reader.GetString(2);
                    listBox1.Items.Add(a);
                    listBox2.Items.Add(b);
                    listBox3.Items.Add((i)+"=>"+c);
                    i += 1;
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                //label1.Text = (ex.Message);
                
            }
            cnn.Close();
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listBox1.SelectedItem == null)
            {
                return;
            }
            Clipboard.SetText(string.Join("\r\n", listBox1.SelectedItem.ToString()));
            //Clipboard.SetText(string.Join("\r\n", listBox1.Items.Cast<object>().Select(x => x.ToString())));
        }

        private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listBox2.SelectedItem == null) {
                return;
            }
            Clipboard.SetText(string.Join("\r\n", listBox2.SelectedItem.ToString()));
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            if (textBox1.Text.Length == 0)
            {
                this.Close();
                索引信息 f;
                f = new 索引信息();
                f.Show(); 
            }
            string box3 = textBox1.Text.ToUpper();
            //try { 
            for (int i = 0; i < listBox2.Items.Count; i++)
            {
                string str = listBox2.Items[i].ToString();
                ///label8.Text= listBox2.Items[i].ToString();
                //label1.Text = label1.Text+'+'+i.ToString();
                int index = str.ToUpper().IndexOf(box3);
                if (index == -1)
                {
                    listBox2.Items.Remove(str);
                    listBox3.Items.RemoveAt(i);
                    listBox1.Items.RemoveAt(i);
                    i -= 1;// 嗯 很精髓
                }
            }
        }
        string Tab = Form1.Tab;
        string Sch = Form1.Sch;
        private void listBox3_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listBox3.SelectedItem == null)
            {
                return;
            }
            int locate = listBox3.SelectedIndex;
            Clipboard.SetText(string.Join("","create or replace index ", Sch, ".", listBox1.Items[locate].ToString()," on ", Sch, ".", Tab,"(", listBox2.Items[locate].ToString(),")"));
            //Clipboard.SetText(string.Join("\r\n", listBox1.Items.Cast<object>().Select(x => x.ToString())));

        }
    }
}
